
//#define FROMMDB
#define FROMEXCEL

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data;


namespace databaseOneOne
{
	class Program
	{
		static void Main(string[] args)
		{
#if FROMMDB
			ReadFromMDB();
#elif FROMEXCEL
			ReadFromExcel();
#endif
		}

		private static void ReadFromExcel()
		{
			string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=northwind.xls;Extended Properties=\"Excel 8.0;HDR=YES;\"";
			String selectStatement = "SELECT CustomerID FROM [Sheet1$]";
			OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);
			//OleDbDataAdapter adapter = new OleDbDataAdapter(selectStatement, myOleDbConnection);
			//DataSet dataSet = new DataSet();
			//adapter.Fill(dataSet);
			//foreach (DataRow row in dataSet.Tables[0].Rows)
			//{
			//    Console.WriteLine(row["Customer ID"]);
			//}

			//myOleDbConnection.Close();

			// create an OleDbCommand object
			OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();

			// set the CommandText property of the OleDbCommand object to
			// a SQL SELECT statement that retrieves a row from the Customers table
			myOleDbCommand.CommandText = "SELECT [Customer ID], [Company Name] FROM [Sheet1$]";
			// open the database connection using the
			// Open() method of the OleDbConnection object
			myOleDbConnection.Open();

			// create an OleDbDataReader object and call the ExecuteReader()
			// method of the OleDbCommand object to run the SELECT statement
			OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();

			while (myOleDbDataReader.Read())
			{
				// display the column values
				Console.WriteLine("myOleDbDataReader[\" CustomerID\"] = " +
				  myOleDbDataReader["Customer ID"]);
				//Console.WriteLine("myOleDbDataReader[\" CompanyName\"] = " +
				//  myOleDbDataReader["Company Name"]);
				//Console.WriteLine("myOleDbDataReader[\" ContactName\"] = " +
				//  myOleDbDataReader["Contact Name"]);
				//Console.WriteLine("myOleDbDataReader[\" Address\"] = " +
				//  myOleDbDataReader["Phone"]);

				Console.WriteLine();
			}
			myOleDbDataReader.Close();
			myOleDbConnection.Close();


			myOleDbConnection.Open();

			// create an OleDbDataReader object and call the ExecuteReader()
			// method of the OleDbCommand object to run the SELECT statement
			myOleDbDataReader = myOleDbCommand.ExecuteReader();

			while (myOleDbDataReader.Read())
			{
				// display the column values
				Console.WriteLine("myOleDbDataReader[\" CustomerID\"] = " +
				  myOleDbDataReader["Customer ID"]);
				Console.WriteLine("myOleDbDataReader[\" CompanyName\"] = " +
				  myOleDbDataReader["Company Name"]);
				//Console.WriteLine("myOleDbDataReader[\" ContactName\"] = " +
				//  myOleDbDataReader["Contact Name"]);
				//Console.WriteLine("myOleDbDataReader[\" Address\"] = " +
				//  myOleDbDataReader["Phone"]);

				Console.WriteLine();
			}
			myOleDbDataReader.Close();
			myOleDbConnection.Close();
		}

		private static void ReadFromMDB()
		{
			// formulate a string containing the details of the
			// database connection
			string connectionString =
			  "provider=Microsoft.Jet.OLEDB.4.0;" +
			  "data source=Nwind.mdb";

			// create an OleDbConnection object to connect to the
			// database, passing the connection string to the constructor
			OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);

			// create an OleDbCommand object
			OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();

			// set the CommandText property of the OleDbCommand object to
			// a SQL SELECT statement that retrieves a row from the Customers table
			myOleDbCommand.CommandText =
			  "SELECT CustomerID, CompanyName, ContactName, Phone " +
			  "FROM Customers " +
			  "WHERE ContactTitle = 'Sales Representative'";
			// open the database connection using the
			// Open() method of the OleDbConnection object
			myOleDbConnection.Open();

			// create an OleDbDataReader object and call the ExecuteReader()
			// method of the OleDbCommand object to run the SELECT statement
			OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();

			// read the row from the OleDbDataReader object using
			// the Read() method

			while (myOleDbDataReader.Read())
			{
				int a = myOleDbDataReader.VisibleFieldCount;

				// display the column values
				Console.WriteLine("myOleDbDataReader[\" CustomerID\"] = " +
				  myOleDbDataReader["CustomerID"]);
				Console.WriteLine("myOleDbDataReader[\" CompanyName\"] = " +
				  myOleDbDataReader["CompanyName"]);
				Console.WriteLine("myOleDbDataReader[\" ContactName\"] = " +
				  myOleDbDataReader["ContactName"]);
				Console.WriteLine("myOleDbDataReader[\" Address\"] = " +
				  myOleDbDataReader["Phone"]);

				Console.WriteLine();
			}

			// close the OleDbDataReader object using the Close() method
			myOleDbDataReader.Close();

			// close the OleDbConnection object using the Close() method
			myOleDbConnection.Close();

		}
	}
}
